using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;   

namespace Metro.Admin.DataAccess
{
    public partial class AdminDataSource
    {
        private List<City> cities = null;

        public ICollection<City> GetCities()
        {
            string id = string.Empty;
            string name = string.Empty;
            string idCountry = string.Empty;
            List<Country> countries = this.GetCountries();
            Country cityCountry = null;

            try
            {
                cities = new List<City>();
                
                string sqlCommand = "SELECT [id],[name],[idCountry] FROM City";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        id = dataReader["id"].ToString();
                        name = dataReader["name"].ToString();
                        idCountry = dataReader["idCountry"].ToString();

                        cityCountry = countries.Find(delegate(Country c) { return c.Id == idCountry; }); 

                        cities.Add(new City(id, name,cityCountry));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return cities;
        }

        public DataTable GetCitiesTable()
        {
            DataSet dsCities = null;

            try
            {
                string sqlCommand = "SELECT [id],[name],[idCountry] FROM City";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                dsCities = metroDb.ExecuteDataSet(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dsCities.Tables[0];
        }
        public void UpdateCity(City city)
        {
            try
            {
                string sqlCommand = "UPDATE [City] SET [name] @name, [idCountry] = @idCountry WHERE id=@id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, city.Id);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, city.Name);
                metroDb.ExecuteNonQuery(dbCommand);

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void CreateCity(City city, string idCountry)
        {
            try
            {
                string sqlCommand = "INSERT INTO [City] ([id],[name],[idCountry]) VALUES (@id,@name,@idCountry)";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, city.Id);
                metroDb.AddInParameter(dbCommand, "@name", DbType.String, city.Name);
                metroDb.AddInParameter(dbCommand, "@idCountry", DbType.String, idCountry);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void DeleteCity(City city)
        {
            try
            {
                string sqlCommand = "DELETE FROM [City] WHERE id=@id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.String, city.Id);
                metroDb.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public City GetCity(int idCity)
        {
            City retCity=null;
            Country cityFromCountry=null; 
            string id = string.Empty;
            string name = string.Empty;
            string idCountry = string.Empty;

            try
            {
                string sqlCommand = "SELECT [id],[name],[idCountry] FROM City WHERE id =@id";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@id", DbType.Int32, idCity);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        id = dataReader["id"].ToString();
                        name = dataReader["name"].ToString();
                        idCountry = dataReader["idCountry"].ToString();

                        cityFromCountry = this.GetCountry(idCountry);

                        retCity = new City(id, name, cityFromCountry); 
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retCity;
        }
        
    }
}
